<%@page language="java" contentType="text/javascript; charset=UTF-8" pageEncoding="UTF-8"%>
<%@page import='java.sql.*' %>
<%@ page import="java.util.Calendar"%>
<%@ page import="java.util.Date"%>
<%@ page import="java.text.DecimalFormat"%>
<%@ page import="java.text.SimpleDateFormat"%>
<%@page import="java.util.ArrayList"%>
<%@page import="net.sf.json.JSONObject"%>
<%@page import="net.sf.json.JSONSerializer"%>
<%@page import="net.sf.json.JSONArray"%>

<%String callback = request.getParameter("callback");%>
<%=callback %> (<%
	
	String mapsearchlat = request.getParameter("mapsearchlat");
	String mapsearchlng = request.getParameter("mapsearchlng");
	
	String p = request.getParameter("pages"); 
    String rpp = request.getParameter("rpp");
    String start = request.getParameter("start");
    int rpps = Integer.parseInt(rpp);
    int starts = Integer.parseInt(start);
	
	Connection conn = null;
	Statement stmt = null;
	PreparedStatement pstmt = null;
    
	JSONObject jo = new JSONObject();
	JSONObject jo2 = new JSONObject();
    JSONArray ja = new JSONArray();
    
    
    try {
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection("jdbc:mysql://localhost/itscreater", "itscreater", "campus123");
		
		stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("select ID, Name, Email, Age, Gender, Nationality, Work, Education, Love, Introduction, Cacao, Language ( 6371 * acos( cos( radians("+mapsearchlat+") ) * cos( radians( UserLat ) ) * cos( radians( UserLng ) - radians("+mapsearchlng+") ) + sin( radians("+mapsearchlat+") ) * sin( radians( UserLat ) ) ) ) AS distance from UserInfo HAVING distance < 20 ORDER BY distance limit "+starts+", "+rpps+";");
		
		while(rs.next()){
			String id = rs.getString("ID");
			String name = rs.getString("Name");
			String email = rs.getString("Email");
			String age = rs.getString("Age");
			String gender = rs.getString("Gender");
			String nationality = rs.getString("Nationality");
			String work = rs.getString("Work");
			String education = rs.getString("Education");
			String love = rs.getString("Love");
			String introduction = rs.getString("Introduction");
			String cacao = rs.getString("Cacao");
			String language = rs.getString("Language");
			
			jo.put("ID", id);
			jo.put("NAME", name);
			jo.put("EMAIL", email);
			jo.put("AGE", age);
			jo.put("GENDER", gender);
			jo.put("NATIONALITY", nationality);
			jo.put("WORK", work);
			jo.put("EDUCATION", education);
			jo.put("LOVE", love);
			jo.put("INTRODUCTION", introduction);
			jo.put("CACAO", cacao);
			jo.put("LANGUAGE", language);
			ja.add(jo);
			jo2.put("results", ja);
			}
		ResultSet rs2 = stmt.executeQuery("select Number, ( 6371 * acos( cos( radians("+mapsearchlat+") ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians("+mapsearchlng+") ) + sin( radians("+mapsearchlat+") ) * sin( radians( Lat ) ) ) ) AS distance from Ting HAVING distance < 20 ORDER BY distance;");
		
		rs2.last();
		int total = rs2.getRow();
		rs2.beforeFirst();
		
		jo2.put("total", new Integer(total));
		out.print(jo2);
    }
    
	finally {
		try { 
            pstmt.close();
        } 
        catch (Exception ignored) {
        }
        try { 
            stmt.close();
        } 
        catch (Exception ignored) {
        }
        try { 
            conn.close();
        } 
        catch (Exception ignored) {
        }
    }
%>);